// Parts Copyright 2008 Newgrove Consultants Limited
// Modified By John Diss 
// From original work:
//
// Copyright 2006 - Ricardo Stuven (rstuven@gmail.com)
//
// This file is part of MsSqlSpatial.
// MsSqlSpatial is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
// 
// MsSqlSpatial is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Lesser General Public License for more details.

// You should have received a copy of the GNU Lesser General Public License
// along with MsSqlSpatial; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA 

using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace MsSqlSpatial
{
    public static class DatabaseHelper
    {
        // 1-item array used as workaround for a "writeable" static readonly field.
        private static readonly string[] contextConnection = new string[1];

        public static SqlConnection ContextConnection
        {
            get { return new SqlConnection(ContextConnectionString); }
        }

        /// <summary>
        /// Use this property for debugging/testing out of SQLCLR context.
        /// </summary>
        public static string ContextConnectionString
        {
            get
            {
                if (SqlContext.IsAvailable)
                    return "context connection=true";
                else
                    return contextConnection[0];
            }
            set { contextConnection[0] = value; }
        }

        internal static bool CheckQueryExists(SqlCommand command, string query, params object[] parameters)
        {
            try
            {
                BuildCommandArray(command, query, parameters);
                object ret = command.ExecuteScalar();
                return (ret != null);
            }
            catch
            {
                return false;
            }
        }

        internal static bool CheckMetaTableExists(SqlCommand command, string tableName)
        {
            return CheckQueryExists(command, string.Format(
                                                 @"SELECT [name] FROM [sys].[objects] WHERE [object_id] = OBJECT_ID(N'[ST].[{0}]') AND [type] = N'U'"
                                                 , tableName));
        }

        internal static bool CheckTriggerExists(SqlCommand command, string schemaName, string triggerName)
        {
            return CheckQueryExists(command,
                                    @"SELECT t.object_id
FROM sys.triggers t, sys.objects o, sys.schemas s
WHERE t.object_id = o.object_id
AND o.schema_id = s.schema_id
AND s.name = @p0
AND t.name = @p1"
                                    , schemaName
                                    , triggerName
                );
        }

        internal static bool CheckColumnExists(SqlCommand command, string schemaName, string tableName,
                                               string columnName)
        {
            return CheckQueryExists(command, string.Format(
                                                 @"SELECT object_id
FROM sys.columns
WHERE object_id = OBJECT_ID(N'[{0}].[{1}]')
AND name = @p0"
                                                 , schemaName
                                                 , tableName
                                                 )
                                    , columnName
                );
        }

        internal static void DropColumn(SqlCommand command, string schemaName, string tableName, string columnName)
        {
            if (CheckColumnExists(command, schemaName, tableName, columnName))
            {
                BuildCommand(command, string.Format(
                                          @"ALTER TABLE [{0}].[{1}]
DROP COLUMN [{2}]
"
                                          , schemaName, tableName, columnName));
                command.ExecuteNonQuery();
            }
        }


        internal static void DropIndex(SqlCommand command, string schemaName, string tableName, string indexName)
        {
            if (CheckIndexExists(command, schemaName, tableName, indexName))
            {
                BuildCommand(command, string.Format(
                                          @"DROP INDEX [{2}] 
ON [{0}].[{1}]
"
                                          , schemaName, tableName, indexName));
                command.ExecuteNonQuery();
            }
        }

        internal static bool CheckIndexExists(SqlCommand command, string schemaName, string tableName, string indexName)
        {
            return CheckQueryExists(command, string.Format(
                                                 @"
SELECT object_id
FROM sys.indexes 
WHERE object_id = OBJECT_ID(N'[{0}].[{1}]')
AND name = @p0"
                                                 , schemaName
                                                 , tableName
                                                 )
                                    , indexName
                );
        }

        internal static bool CheckConstraintExists(SqlCommand command, string schemaName, string tableName,
                                                   string constraintName)
        {
            return CheckQueryExists(command, string.Format(
                                                 @"SELECT object_id
FROM sys.check_constraints 
WHERE object_id = OBJECT_ID(N'[{0}].[{2}]')
AND parent_object_id = OBJECT_ID(N'[{0}].[{1}]')"
                                                 , schemaName
                                                 , tableName
                                                 , constraintName
                                                 ));
        }

        internal static string CheckSchemaName(SqlCommand command, SqlString schemaName)
        {
            return CheckSchemaName(command, schemaName.ToString());
        }

        internal static string CheckSchemaName(SqlCommand command, string schemaName)
        {
            string checkedSchema = "dbo";
            if (schemaName.Trim().Length != 0)
            {
                bool schemaExists = CheckQueryExists(command,
                                                     @"SELECT [name] FROM [sys].[schemas] WHERE [name] = @p0"
                                                     , schemaName);

                if (schemaExists)
                    checkedSchema = schemaName;
            }
            return checkedSchema;
        }

        internal static void ExecuteDropTable(SqlCommand command, string schemaName, string tableName)
        {
            BuildCommand(command, string.Format(
                                      @"DROP TABLE [{0}].[{1}]"
                                      , schemaName, tableName));
            command.ExecuteNonQuery();
        }

        internal static void SplitSchemaAndTableName(SqlCommand command, ref string tableName, ref string schemaName)
        {
            int schemaDot = tableName.IndexOf(".");
            if (schemaDot != -1)
            {
                schemaName = tableName.Substring(0, schemaDot);
                tableName = tableName.Substring(schemaDot + 1);
            }
            schemaName = CheckSchemaName(command, schemaName);
        }

        internal static void BuildCommand(SqlCommand command, string commandText, params object[] parameters)
        {
            BuildCommandArray(command, commandText, parameters);
        }

        internal static void BuildCommandArray(SqlCommand command, string commandText, object[] parameters)
        {
            command.CommandText = commandText;
            command.CommandType = CommandType.Text;
            command.Parameters.Clear();
            for (int i = 0; i < parameters.Length; i++)
                command.Parameters.AddWithValue("@p" + i, parameters[i]);
        }
    }
}